T-SQL Fundamentals, Third Edition by Ben-Gan Itzik

Author:Ben-Gan, Itzik
Language: eng
Format: epub
Publisher: Pearson Education
Published: 2016-07-29T04:00:00+00:00

T-SQL does not provide a built-in EXCEPT ALL operator, but you can provide an alternative of your own similar to how you handled INTERSECT ALL. Namely, add a ROW_NUMBER calculation to each of the input queries to number the occurrences of the rows, and use the EXCEPT operator between the two input queries. Only occurrences that don’t have matches will be returned.

The following code returns occurrences of employee locations that have no corresponding occurrences of customer locations:

Click here to view code image

WITH EXCEPT_ALL

AS

(

SELECT

ROW_NUMBER()

OVER(PARTITION BY country, region, city

ORDER BY (SELECT 0)) AS rownum,

country, region, city

FROM HR.Employees

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.